package excelrept

import (
	"bytes"
	"fmt"
	"math"
	"strconv"
	"strings"

	"github.com/xuri/excelize/v2"
	"shqsoft.cn/pas3ui/base"
)

//VerifyInfo 校验信息
type VerifyInfo struct {
	Row        int    `json:"row"`
	Col        int    `json:"col"`
	Difference string `json:"difference"`
	Cell       string `json:"cell"`
	Cell2      string `json:"cell2"`
	Info       string `json:"info"`
}

//VerifyData 校验数据
func VerifyData(reptName string, params map[string]string) (infos map[string][]VerifyInfo, err error) {
	conf, err := getConfig(reptName)
	if err != nil {
		return nil, err
	}
	infos = make(map[string][]VerifyInfo, 4)
	for _, t := range conf.Tables {
		is, err := verifyTable(reptName, &t, params)
		if err != nil {
			return nil, err
		}
		sInfo, ok := infos[t.Sheet]
		if !ok {
			sInfo = make([]VerifyInfo, 0)
		}
		sInfo = append(sInfo, is...)
		infos[t.Sheet] = sInfo
	}
	return
}
func verifyTable(reptName string, tConf *Table, params map[string]string) (infos []VerifyInfo, err error) {
	infos = make([]VerifyInfo, 0, 5)
	var info string
	tpl, err := GetPageData(reptName, params, nil)
	if err != nil {
		return nil, err
	}
	var utid int
	utid, err = strconv.Atoi(params["TBJG"])
	if err != nil {
		return nil, err
	}
	tItem := tpl.getTableItem(tConf.Name)
	calc := NewExpCalc(tItem)
	var lexp, rexp, relation string
	var valMap1, valMap2 map[string]float64
	var fcell string
	var calc2 *ExpCalc
	refCalcMap := make(map[string]*ExpCalc, 2)
	for _, ver := range tConf.Verifies {
		if utid != 0 && !base.IsInSliceForInt(ver.Utids, utid) {
			continue
		}
		lexp, rexp, relation, err = parseConditionExp(ver.Exp)
		if err != nil {
			return nil, err
		}
		valMap1, fcell, err = calc.CalcReptExp(lexp, ver.Range)
		if err != nil {
			return nil, err
		}
		var ok bool
		if ver.RefRept == "" && ver.RefTable == "" {
			calc2 = calc
		} else if ver.RefRept != "" {
			key := ver.RefRept + ver.RefTable
			if calc2, ok = refCalcMap[key]; !ok {
				tpl2, err := GetPageData(ver.RefRept, params, nil)
				if err != nil {
					return nil, err
				}
				tItem = tpl2.getTableItem(ver.RefTable)
				calc2 = NewExpCalc(tItem)
				refCalcMap[key] = calc2
			}
		} else {
			key := ver.RefTable
			if calc2, ok = refCalcMap[key]; !ok {
				tItem = tpl.getTableItem(ver.RefTable)
				calc2 = NewExpCalc(tItem)
				refCalcMap[key] = calc2
			}
		}
		valMap2, _, err = calc2.CalcReptExp(rexp, ver.Range)
		if err != nil {
			return nil, err
		}
		var colStr string
		for k, v1 := range valMap1 {
			info = ""
			var difference, abs float64
			v2 := valMap2[k]
			k2 := getRefCell2(reptName, k)
			if relation == "=" {
				if math.Abs(v1-v2) > 0.001 {
					info = getVerifyInfo(k2, &ver, relation, v1, v2)
					abs = math.Abs(v1 - v2)
					difference = math.Trunc(abs*1e2+0.5) * 1e-2
				}
			} else if relation == ">" {
				if v1-v2 <= 0.001 {
					info = getVerifyInfo(k2, &ver, relation, v1, v2)
					abs = math.Abs(v1 - v2)
					difference = math.Trunc(abs*1e2+0.5) * 1e-2
				}
			} else if relation == ">=" {
				if math.Abs(v1-v2) > 0.001 && v1-v2 < 0.001 {
					info = getVerifyInfo(k2, &ver, relation, v1, v2)
					abs = math.Abs(v1 - v2)
					difference = math.Trunc(abs*1e2+0.5) * 1e-2
				}
			} else if relation == "<" {
				if v1-v2 >= 0.001 {
					info = getVerifyInfo(k2, &ver, relation, v1, v2)
					abs = math.Abs(v1 - v2)
					difference = math.Trunc(abs*1e2+0.5) * 1e-2
				}
			} else if relation == "<=" {
				if v1-v2 > 0.001 {
					info = getVerifyInfo(k2, &ver, relation, v1, v2)
					abs = math.Abs(v1 - v2)
					difference = math.Trunc(abs*1e2+0.5) * 1e-2
				}
			} else {
				err = fmt.Errorf("系统校验不支持的比较关系符%s", relation)
				return nil, err
			}
			if info != "" {
				col, row, err := parseAxis(fcell)
				if err != nil {
					return nil, err
				}
				var row2, col2 int
				var cell2Str string
				expType := getExpType(ver.Exp)
				if k != "" && expType == "01" {
					col, err = excelize.ColumnNameToNumber(k)
					if err != nil {
						return nil, err
					}
					col2 = col
					col += tConf.ColStart - 1
					colStr, err = excelize.ColumnNumberToName(col)
					if err != nil {
						return nil, err
					}
					fcell = colStr + strconv.Itoa(row)
					row2 = row - tConf.RowStart + 1
					cell2Str, err = excelize.ColumnNumberToName(col2)
					if err != nil {
						return nil, err
					}
					cell2Str = getRefCell2(reptName, cell2Str)
					cell2Str = cell2Str + strconv.Itoa(row2)
				} else if k != "" && expType == "10" {
					col2 = col - tConf.ColStart + 1
					colStr, err = excelize.ColumnNumberToName(col)
					if err != nil {
						return nil, err
					}
					row2, _ = strconv.Atoi(k)
					cell2Str, err = excelize.ColumnNumberToName(col2)
					if err != nil {
						return nil, err
					}
					row = row2 + tConf.RowStart - 1
					fcell = colStr + strconv.Itoa(row)
					cell2Str = getRefCell2(reptName, cell2Str)
					cell2Str = cell2Str + strconv.Itoa(row2)
				} else {
					col2 = col - tConf.ColStart + 1
					row2 = row - tConf.RowStart + 1
					cell2Str, err = excelize.ColumnNumberToName(col2)
					if err != nil {
						return nil, err
					}
					cell2Str = getRefCell2(reptName, cell2Str)
					cell2Str = cell2Str + strconv.Itoa(row2)
				}
				infos = append(infos, VerifyInfo{
					Cell:       fcell,
					Info:       info,
					Col:        col,
					Row:        row,
					Difference: fmt.Sprintf("%.2f", difference),
					Cell2:      cell2Str,
				})
			}
		}
	}
	return
}
func getVerifyInfo(rg string, ver *Verify, relation string, v1, v2 float64) string {
	exp := ver.Exp
	if ver.Caption != "" {
		exp = ver.Caption
	}
	var buff bytes.Buffer
	if ver.RefRept != "" {
		buff.WriteString("与表")
		buff.WriteString(ver.RefRept)
	}
	if rg != "" {
		if isNumber(rg[0]) {
			buff.WriteString(fmt.Sprintf("在%s行上", rg))
		} else {
			buff.WriteString(fmt.Sprintf("在%s列上", rg))
		}
	}
	buff.WriteString("不满足校验规则")
	buff.WriteString(exp)
	buff.WriteString(",")
	buff.WriteString(fmt.Sprintf("左值:%.2f", v1))
	buff.WriteString(",")
	buff.WriteString(fmt.Sprintf("右值:%.2f", v2))
	return buff.String()
}

func getRefCell2(reptName, k string) string {
	if reptName == "SF6303-211" {
		if k == "D" {
			k = "[C1]"
		} else if k == "E" {
			k = "D"
		} else if k == "F" {
			k = "E"
		} else if k == "G" {
			k = "F"
		} else if k == "H" {
			k = "G"
		} else if k == "I" {
			k = "H"
		}
	} else if reptName == "SF7101-201" {
		if k == "G" {
			k = "[A0]"
		} else if k == "H" {
			k = "[B0]"
		} else if k == "I" {
			k = "[C0]"
		} else if k == "J" {
			k = "[D0]"
		} else if k == "K" {
			k = "[E0]"
		} else if k == "L" {
			k = "[F0]"
		} else if k == "M" {
			k = "[G0]"
		} else if k == "N" {
			k = "[A5]"
		} else if k == "O" {
			k = "[B5]"
		} else if k == "P" {
			k = "[C5]"
		} else if k == "Q" {
			k = "[D5]"
		} else if k == "R" {
			k = "[E5]"
		} else if k == "S" {
			k = "[F5]"
		} else if k == "T" {
			k = "[G5]"
		} else if k == "U" {
			k = "[A1]"
		} else if k == "V" {
			k = "[B1]"
		} else if k == "W" {
			k = "[C1]"
		} else if k == "X" {
			k = "[D1]"
		} else if k == "Y" {
			k = "[E1]"
		} else if k == "Z" {
			k = "[F1]"
		} else if k == "AA" {
			k = "[A2]"
		} else if k == "AB" {
			k = "[B2]"
		} else if k == "AC" {
			k = "[C2]"
		} else if k == "AD" {
			k = "[D2]"
		} else if k == "AE" {
			k = "[E2]"
		} else if k == "AF" {
			k = "[F2]"
		} else if k == "AG" {
			k = "[A3]"
		} else if k == "AH" {
			k = "[B3]"
		} else if k == "AI" {
			k = "[C3]"
		} else if k == "AJ" {
			k = "[D3]"
		} else if k == "AK" {
			k = "[E3]"
		} else if k == "AL" {
			k = "[F3]"
		} else if k == "AM" {
			k = "[A4]"
		} else if k == "AN" {
			k = "[B4]"
		} else if k == "AO" {
			k = "[C4]"
		} else if k == "AP" {
			k = "[D4]"
		} else if k == "AQ" {
			k = "[E4]"
		} else if k == "AR" {
			k = "[F4]"
		}
	} else if reptName == "Q0711" {
		if k == "E" {
			k = "[D1]"
		} else if k == "F" {
			k = "E"
		} else if k == "G" {
			k = "[E1]"
		} else if k == "H" {
			k = "F"
		}
	}
	return k
}

func getRefVal(ver *Verify, params map[string]string) (sheet string, val float64, err error) {
	conf, err := getConfig(ver.RefRept)
	if err != nil {
		return "", 0, err
	}
	var tConf *Table
	for _, t := range conf.Tables {
		if t.Name == ver.RefTable {
			tConf = &t
			break
		}
	}
	if tConf == nil {
		err = fmt.Errorf("报表%s中没有找到表%s", ver.RefRept, tConf.Name)
	}
	sheet = tConf.Sheet
	cells, minus, err := parseExp(ver.Exp)
	if err != nil {
		return "", 0, err
	}
	for i, cell := range cells {
		v, err := getCellVal(tConf, cell, params)
		if err != nil {
			return "", 0, err
		}
		if base.IsInSliceForInt(minus, i) {
			v = -v
		}
		val += v
	}
	return
}

func parseExp(exp string) (cells []string, minus []int, err error) {
	if exp == "" {
		return
	}
	ebs := []byte(exp)
	var bi int
	bz := 'F' //标记，F:开始，A:字母，N:数字,M:字母或数字
	cells = make([]string, 0, 5)
	minus = make([]int, 0, 5)
	cellbs := make([]byte, 0, 5)
	index := 0
	for i, b := range ebs {
		bi = int(b)
		if bz == 'F' {
			if bi >= 65 && bi <= 90 {
				bz = 'M'
				cellbs = append(cellbs, b)
			} else if bi == 43 || bi == 45 {
				if bi == 45 {
					minus = append(minus, index)
				}
				bz = 'A'
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位是大写字母或者+，-符号", exp, i)
				return
			}
		} else if bz == 'A' {
			if bi >= 65 && bi <= 90 {
				bz = 'M'
				cellbs = append(cellbs, b)
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位应该是大写字母", exp, i)
				return
			}
		} else if bz == 'M' {
			if bi >= 65 && bi <= 90 {
				bz = 'M'
				cellbs = append(cellbs, b)
			} else if bi > 48 && bi <= 57 {
				bz = 'N'
				cellbs = append(cellbs, b)
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位应该是大写字母或者数字，但不能为0", exp, i)
				return
			}
		} else if bz == 'N' {
			if bi >= 48 && bi <= 57 {
				bz = 'N'
				cellbs = append(cellbs, b)
			} else if bi == 43 || bi == 45 {
				cells = append(cells, string(cellbs))
				cellbs = make([]byte, 0, 5)
				index++
				if bi == 45 {
					minus = append(minus, index)
				}
				bz = 'A'
			} else {
				err = fmt.Errorf("错误的计算表达式%s，表达式的第%d位应该是数字或者+，-符号", exp, i)
				return
			}
		}
	}
	if bz != 'N' {
		err = fmt.Errorf("错误的计算表达式%s，表达式不完整", exp)
	}
	cells = append(cells, string(cellbs))
	return
}
func getCellVal(tConf *Table, cell string, params map[string]string) (val float64, err error) {
	var col, rowID int
	col, rowID, err = parseAxis(cell)
	if err != nil {
		return
	}
	colNum, err := excelize.ColumnNumberToName(col)
	if err != nil {
		return
	}
	colStr, ok := tConf.Columns[colNum]
	if !ok {
		err = fmt.Errorf("列%s在表%s的配置中没有找到对应的字段", colNum, tConf.Name)
	}
	var buff bytes.Buffer
	buff.WriteString("select ")
	buff.WriteString(colStr)
	buff.WriteString(" from ")
	buff.WriteString(tConf.Name)
	plen := len(params)
	pvals := make([]interface{}, plen+1, plen+1)
	dbCols, err := getDBColumns(tConf.Name)
	if err != nil {
		return 0, err
	}
	if plen > 0 {
		buff.WriteString(" where ")
		var convF ConvertFunc
		i := 0
		for k, v := range params {
			convF = nil
			for _, col := range dbCols {
				if strings.ToUpper(col.Name) == strings.ToUpper(k) {
					convF = col.convertFunc
					break
				}
			}
			if convF == nil {
				return 0, fmt.Errorf("参数名%s在表%s中没有定义的字段", k, tConf.Name)
			}
			buff.WriteString(k)
			buff.WriteString("=?")
			buff.WriteString(" AND ")
			pvals[i], err = convF(v)
			if err != nil {
				return 0, err
			}
			i++
		}
	} else {
		buff.WriteString(" where ")
	}
	buff.WriteString(tConf.RowNoCol)
	buff.WriteString("=?")
	rowID = rowID - tConf.RowStart + 1
	pvals[plen] = rowID
	strSQL := buff.String()
	row := base.CHDB.Raw(strSQL, pvals...).Row()
	var v *float64
	err = row.Scan(&v)
	if err != nil || v == nil {
		err = fmt.Errorf("报表%s的单元格%s为空，是否没有填报", tConf.Sheet, cell)
		return
	}
	val = *v
	return
}
